package cn.com.surker.production.util;

import cn.com.surker.enums.production.car.CarModelTypeEnum;
import cn.com.surker.enums.production.car.MortarBillTypeEnum;
import cn.com.surker.enums.system.BillPriceTypeEnum;
import cn.com.surker.excel.ExcelStyleUtils;
import cn.com.surker.production.entity.ProductionMortarBill;
import cn.com.surker.production.entity.ProductionPumpBill;
import cn.com.surker.production.entity.ProductionTransportBill;
import cn.com.surker.production.entity.ProductionWaterBill;
import cn.com.surker.util.DateUtil;
import cn.com.surker.util.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author lucky
 * @create 2023年10月16 10:36
 * @desc
 */
public class ExcelProductionReportUtils {


    /**
     * 泵送价税版结算单
     * @return
     */
	public static XSSFWorkbook pumpBillWorkbook(String sheetName, String title, Double rate, String orgName, String startTime, String endTime,
													   String supplier, Map<Long, String> typeMap, Map<Long, String> normsMap, String creator,List<ProductionPumpBill> list){
		XSSFWorkbook wb  = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		sheet.setColumnWidth(0, 5590);
		sheet.setColumnWidth(1, 7590);
		sheet.setColumnWidth(2, 5590);
		sheet.setColumnWidth(3, 3670);
		sheet.setColumnWidth(4, 5590);
		sheet.setColumnWidth(5, 5590);

		Integer rowHeight = 500;
		sheet.setMargin(Sheet.LeftMargin, 0.2);
		// 右边距
		sheet.setMargin(Sheet.RightMargin, 0.1);
		// 上边距
		sheet.setMargin(Sheet.TopMargin, 0.5);
		// 下边距
		sheet.setMargin(Sheet.BottomMargin, 0.4);
		// 页眉边距
		sheet.setMargin(Sheet.HeaderMargin, 0.2);
		// 页脚边距
		sheet.setMargin(Sheet.FooterMargin, 0);
		//添加表头第1行
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
		printSetup.setScale((short) 60);
		printSetup.setFitWidth((short)1);
		printSetup.setFitHeight((short)0);
		Footer footer = sheet.getFooter();
		footer.setCenter( "第 &P 页，共 &N 页" );
		//合并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 0, 5);//合并表头
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 0, 1);//结算时间
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 2, 3);//结算时间
		sheet.addMergedRegion(region);
		//指定行高
		XSSFCellStyle oneStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		oneStyle.setFont(ExcelStyleUtils.hssfFont(wb, 18, "宋体", IndexedColors.BLACK.index, true));
		oneStyle.setWrapText(true);//自动换行
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 18, "宋体", IndexedColors.BLACK.index, true));
		titleStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueRightStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueRightStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueRightStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueLeftStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueLeftStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueLeftStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
		countStyle.setWrapText(true);//自动换行
//		countStyle.setVerticalAlignment(XSSFCellStyle.ALIGN_LEFT);
//		countStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.RED.index, true));
		XSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		successStyle.setWrapText(true);//自动换行

		Object[] titleObjs = {title,"","","","",""};
		createDetailRow(sheet, 0, (short) 600, titleStyle, titleObjs);
		valueRightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueRightStyle.setAlignment(HorizontalAlignment.RIGHT);

		valueLeftStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueLeftStyle.setAlignment(HorizontalAlignment.LEFT);
		Object[] billTimeObjs = {"承租方:" + orgName,"","制表时间:" + DateUtil.convertString(new Date(), "yyyy.MM.dd"),"", "增值税税率", rate + "%"};
		XSSFCellStyle[] cellStyles = {valueLeftStyle,valueLeftStyle,valueStyle,valueStyle,valueStyle,valueStyle};
		createDetailRowV1(sheet, 1, (short) 500, cellStyles, billTimeObjs);
		Object[] subTitleObjs = {"泵送方式","时间","泵送方量(m³)","单价(元)","金额(元)","备注"};
		createDetailRow(sheet, 2, (short) 600, successStyle, subTitleObjs);

		Integer index = 3;
		Double sumStere = 0.0;//方量
		Double sumPrice = 0.0;//总金额
		String time = startTime.substring(0, 10).replaceAll("-", ".") + "--"
				+ endTime.substring(0, 10).replaceAll("-", ".") ;
		for (int i = 0; i < list.size(); i++) {
			ProductionPumpBill bill = list.get(i);
			String normsName = "";
			//正常结算的累加结算方量
			String remark = "";
			if (!StringUtils.isNull(bill.getPriceType()) && (bill.getPriceType() == BillPriceTypeEnum.JS.getCode()
					|| bill.getPriceType() == BillPriceTypeEnum.CZ.getCode())) {
				sumStere = StringUtils.addDouble(sumStere, bill.getStere());
				normsName = StringUtils.convertString(typeMap.get(StringUtils.convertLong(bill.getPumpType())))
						+ StringUtils.convertString(normsMap.get(StringUtils.convertLong(bill.getPumpNorms())));
			} else {
				normsName = StringUtils.convertString(typeMap.get(StringUtils.convertLong(bill.getPumpType())))
						+ StringUtils.convertString(normsMap.get(StringUtils.convertLong(bill.getPumpNorms())))
						+ "(补)";
				remark = bill.getRemark();
			}
			sumPrice = StringUtils.addDouble(sumPrice, bill.getBillMoney());

			Object[] valueObjs = {normsName, time, bill.getStere(),
					bill.getPrice(), bill.getBillMoney(), remark};
			createDetailRow(sheet, index, (short) 500, successStyle, valueObjs);
			index++;
		}
		sumStere = StringUtils.formatDouble(sumStere, 2);
		sumPrice = StringUtils.formatDouble(sumPrice, 2);
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		Object[] countObjs = {"小计:", "", sumStere, "", sumPrice, ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[] {"合计(元):", "", "", "", sumPrice, ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[] {"结算金额:", "", StringUtils.toMoneyUpperCase(StringUtils.formatDouble(sumPrice, 2)), "", "", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;

		Double tempNoTaxPrice = StringUtils.formatDouble(sumPrice / (1 + rate / 100), 2);
		Double tempTaxPrice = StringUtils.formatDouble(tempNoTaxPrice * (rate / 100), 2);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[] {"不含税金额:", tempNoTaxPrice, "",
				"增值税税额", tempTaxPrice, ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;

		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"承租单位:", orgName,"",
				"出租单位:",supplier,""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"制表:", creator, "", "经办人:", "", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"生产部:","",  "", "审核:","", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"财务部:","", "", "结算日期:","", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		return wb;
	}

	public static XSSFWorkbook getProductionPlanHSSFWorkbook(String sheetName, String title, List<Map<String, Object>> list,
															 XSSFWorkbook wb, Integer isFinance, Map<Integer, String> typeMap){
		if(wb == null){
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		sheet.setColumnWidth(0, 2400);
		sheet.setColumnWidth(1, 2400);
		sheet.setColumnWidth(2, 3400);
		sheet.setColumnWidth(3, 3400);
		sheet.setColumnWidth(4, 3400);
		sheet.setColumnWidth(5, 3400);
		sheet.setColumnWidth(6, 3400);
		sheet.setColumnWidth(7, 3400);
		sheet.setColumnWidth(8, 3400);
		sheet.setColumnWidth(9, 3400);
		sheet.setColumnWidth(10, 3400);
		sheet.setColumnWidth(11, 3400);
		sheet.setColumnWidth(12, 3400);
		sheet.setColumnWidth(13, 3400);
		sheet.setColumnWidth(14, 3400);
		sheet.setColumnWidth(15, 3400);
		sheet.setColumnWidth(16, 3400);
		sheet.setColumnWidth(17, 3400);
		sheet.setColumnWidth(18, 5400);
		sheet.setColumnWidth(19, 5400);
		sheet.setColumnWidth(20, 5400);

		//锁定列或行，参数1锁定左边列，参数2锁定行，参数3显示列，参数4显示行
		sheet.createFreezePane(0, 3, 0 ,3);
		//添加表头第1行
		XSSFRow oneRow = sheet.createRow(0);
		oneRow.setHeight((short) 700);

		//合并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 0, 20);//合并表头
		sheet.addMergedRegion(region);
		for (int i = 0; i < 21; i++) {
			if (i == 4) {
				region=new CellRangeAddress(1, 1, 4, 6);//合并表头
				sheet.addMergedRegion(region);
			}
			if (i == 7 || i == 9 || i == 11 || i == 13 || i == 15) {
				region=new CellRangeAddress(1, 1, i, (i + 1));//合并表头
				sheet.addMergedRegion(region);
			}
			if (i > 4 || i < 17) {
				region=new CellRangeAddress(1, 2, i, i);//合并表头
				sheet.addMergedRegion(region);
			}
		}
		//指定行高
//		threeRow.setHeight((short) 700);
		XSSFCellStyle oneStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		oneStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, null));
        oneStyle.setWrapText(true);//自动换行
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, null));
        titleStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
        valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
		countStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//设置背景色
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		countStyle.setWrapText(true);//自动换行
		XSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.LIME.index);
		successStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//设置背景色
		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.RED.index, null));

		XSSFCell cell = oneRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(title);
		for (int i = 1; i < 21; i++) {
			cell = oneRow.createCell(i);//标题
			cell.setCellStyle(titleStyle);
		}
//		XSSFRow twoRow = sheet.createRow(1);
		Object[] titleObjs = {"站点","联系人","供应商","类型","截止24日未付余额",
				"","","累计供应金额(截止时间每月24日)","", "累计付款金额","","上月供应金额","","上月付款金额","",
				"本月付款金额","","运输款未开票金额(元)","保证金","计划付款(元)", "合同付款条件及备注"};
		createDetailRow(sheet, 1, (short) 500, countStyle, titleObjs);
		Object[] titleObjs1 = {"","","","",
				"未付款","未付运输款","未付油款","运输款(元)","油款(元)",
				"运输款(元)","油款(元)","运输款(元)","油款(元)","运输款(元)","油款(元)","运输款(元)","油款(元)","","","",""};
		createDetailRow(sheet, 2, (short) 500, countStyle, titleObjs1);

		Integer index = 3;

		Double sumUnPayment = 0.0;//实时未付余额
		Double sumUnTransportPayment = 0.0;//实时未付余额运输款
		Double sumUnOilPayment = 0.0;//实时未付余额含未付油款
		Double sumTotalSettlement = 0.0;//累计结算金额
		Double sumTotalOilMoney = 0.0;//累计油款结算金额
		Double sumTotalPayment = 0.0;//累计付款金额
		Double sumTotalOilPayment = 0.0;//累计付款金额
		Double sumTotalTicket = 0.0;//累计开票金额
		Double sumLastSettlement = 0.0;//上月结算金额
		Double sumLastOilMoney = 0.0;//上月油款结算金额
		Double sumLastPayment = 0.0;//上月付款金额
		Double sumLastOilPayment = 0.0;//上月油款付款金额
		Double sumMonthPayment = 0.0;//本月付款金额*
		Double sumMonthOilPayment = 0.0;//本月油款付款金额
		Double totalEnsureMoney = 0.0;//累计保证金
		Double sumPlanMoney = 0.0;//计划付款金额
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			String orgName = StringUtils.convertString(map.get("org_name"));//公司名称
			String supplier = StringUtils.convertString(map.get("supplier"));//供应商
			Integer supplierId = StringUtils.convertInteger(map.get("supplier_id"));//供应商ID
			String link = StringUtils.convertString(map.get("link"));//联系人
			String remark = StringUtils.convertString(map.get("remark"));//备注
			Integer type = StringUtils.convertInteger(map.get("type"));//合同类型
			Double totalSettlement = StringUtils.convertDouble(map.get("totalSettlement"));//累计结算金额
			Double totalOilMoney = StringUtils.convertDouble(map.get("totalOilMoney"));//累计油款结算金额
			Double totalPayment = StringUtils.convertDouble(map.get("totalPayment"));//累计付款金额
			Double totalOilPayment = StringUtils.convertDouble(map.get("totalOilPayment"));//累计付款金额
			Double lastSettlement = StringUtils.convertDouble(map.get("lastSettlement"));//上月结算金额
			Double lastOilMoney = StringUtils.convertDouble(map.get("lastOilMoney"));//上月油款结算金额
			Double lastPayment = StringUtils.convertDouble(map.get("lastPayment"));//上月付款金额
			Double lastOilPayment = StringUtils.convertDouble(map.get("lastOilPayment"));//上月油款付款金额
//			Double monthSettlement = StringUtils.convertDouble(map.get("monthSettlement"));//本月结算金额
//			Double monthOilMoney = StringUtils.convertDouble(map.get("monthOilMoney"));//本月结算金额
			Double monthPayment = StringUtils.convertDouble(map.get("monthPayment"));//本月付款金额
			Double monthOilPayment = StringUtils.convertDouble(map.get("monthOilPayment"));//本月油款付款金额
			Double totalTicket = StringUtils.convertDouble(map.get("totalTicket"));//累计开票金额
			Double ensureMoney = StringUtils.convertDouble(map.get("totalEnsureMoney"));//保证金
			Double planMoney = StringUtils.convertDouble(map.get("planMoney"));//计划付款金额
			String typeTip = typeMap.get(type);
			if (isFinance == 1)
				link = "";

			Double tempUnTransportPayment = StringUtils.formatDouble(StringUtils.subtractDouble(totalSettlement, totalPayment + monthPayment), 2);//实时未付余额运输款
			Double tempUnOilPayment = StringUtils.formatDouble(StringUtils.subtractDouble(totalOilMoney, totalOilPayment + monthOilPayment), 2);//实时未付余额含未付油款
			Double tempUnPayment = StringUtils.addDouble(tempUnTransportPayment, tempUnOilPayment);//实时未付余额
			Double tempTicket = StringUtils.formatDouble(StringUtils.subtractDouble(totalSettlement, totalTicket), 2);//未开票金额

			sumUnPayment = StringUtils.formatDouble(StringUtils.addDouble(tempUnPayment, tempUnPayment), 2);//实时未付余额
			sumUnTransportPayment = StringUtils.formatDouble(StringUtils.addDouble(sumUnTransportPayment, tempUnTransportPayment), 2);//运输款实时未付余额
			sumUnOilPayment = StringUtils.formatDouble(StringUtils.addDouble(sumUnOilPayment, tempUnOilPayment), 2);//油款未付金额
			sumTotalSettlement = StringUtils.formatDouble(StringUtils.addDouble(sumTotalSettlement, totalSettlement), 2);//累计运输付款金额
			sumTotalOilMoney = StringUtils.formatDouble(StringUtils.addDouble(sumTotalOilMoney, totalOilMoney), 2);//累计油款结算金额
			sumTotalPayment = StringUtils.formatDouble(StringUtils.addDouble(sumTotalPayment,totalPayment), 2);//累计付款金额
			sumTotalOilPayment = StringUtils.formatDouble(StringUtils.addDouble(sumTotalOilPayment, totalOilPayment), 2);//累计油款付款金额
			sumTotalTicket = StringUtils.formatDouble(StringUtils.addDouble(sumTotalTicket, tempTicket), 2);//累计开票金额
			sumLastSettlement = StringUtils.formatDouble(StringUtils.addDouble(sumLastSettlement, lastSettlement), 2);//上月运输结算金额
			sumLastOilMoney = StringUtils.formatDouble(StringUtils.addDouble(sumLastOilMoney, lastOilMoney), 2);//上月油款结算金额
			sumLastPayment = StringUtils.formatDouble(StringUtils.addDouble(sumLastPayment, lastPayment), 2);//上月付款金额
			sumLastOilPayment = StringUtils.formatDouble(StringUtils.addDouble(sumLastOilPayment, lastOilPayment), 2);//上月油款付款金额
			sumMonthPayment = StringUtils.formatDouble(StringUtils.addDouble(sumMonthPayment, monthPayment), 2);//本月付款金额
			sumMonthOilPayment = StringUtils.formatDouble(StringUtils.addDouble(sumMonthOilPayment, monthOilPayment), 2);//本月油款付款金额
			sumPlanMoney = StringUtils.formatDouble(StringUtils.addDouble(sumPlanMoney, planMoney), 2);//计划付款金额
			totalEnsureMoney = StringUtils.formatDouble(StringUtils.addDouble(totalEnsureMoney, ensureMoney), 2);//保证金

			Object[] valueObjs = {orgName,link,supplier,typeTip,
					tempUnPayment, tempUnTransportPayment, tempUnOilPayment, totalSettlement, totalOilMoney,
					totalPayment,totalOilPayment,lastSettlement,lastOilMoney,lastPayment,
					lastOilPayment, monthPayment, monthOilPayment,totalTicket,ensureMoney,planMoney,remark};
			createDetailRow(sheet, index , (short) 500, valueStyle, valueObjs);
			index++;
		}

		Object[] valueObjs = {"合计","","","",
				sumUnPayment, sumUnTransportPayment, sumUnOilPayment, sumTotalSettlement, sumTotalOilMoney,
				sumTotalPayment,sumTotalOilPayment,sumLastSettlement,sumLastOilMoney,sumLastPayment,
				sumLastOilPayment, sumMonthPayment, sumMonthOilPayment,sumTotalTicket,totalEnsureMoney,sumPlanMoney,""};
		createDetailRow(sheet, index , (short) 500, successStyle, valueObjs);

		return wb;
	}

	/**
	 * 导出水稳层结算单报表
	 * @param sheetName
	 * @param title
	 * @param list
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook getWaterBillHSSFWorkbook(String sheetName, String title, String today, String startTime, String endTime,
														String supplier, String creator, List<ProductionWaterBill> list, XSSFWorkbook wb){
		if(wb == null){
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		sheet.setColumnWidth(0, 5590);
		sheet.setColumnWidth(1, 3670);
		sheet.setColumnWidth(2, 3670);
		sheet.setColumnWidth(3, 3670);
		sheet.setColumnWidth(4, 3670);
		sheet.setColumnWidth(5, 3670);
		sheet.setColumnWidth(6, 3670);
		sheet.setColumnWidth(7, 3670);
		sheet.setColumnWidth(8, 3670);
		sheet.setColumnWidth(9, 3670);

		//合并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 0, 9);//合并表头
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 0, 9);//结算时间
		sheet.addMergedRegion(region);
		//指定行高
		XSSFCellStyle oneStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		oneStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		oneStyle.setWrapText(true);//自动换行
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		titleStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueRightStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueRightStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueRightStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueLeftStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueLeftStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueLeftStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
		countStyle.setWrapText(true);//自动换行
//		countStyle.setVerticalAlignment(XSSFCellStyle.ALIGN_LEFT);
//		countStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.RED.index, true));
		XSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		successStyle.setWrapText(true);//自动换行


		Object[] titleObjs = {title,"","","","","", "","","",""};
		createDetailRow(sheet, 0, (short) 600, titleStyle, titleObjs);
		Object[] billTimeObjs = {"结算时间:" + startTime.substring(0, 10) + "--" + endTime.substring(0, 10),"","","","","", "","","",""};
		valueRightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueRightStyle.setAlignment(HorizontalAlignment.RIGHT);
		createDetailRow(sheet, 1, (short) 500, valueRightStyle, billTimeObjs);

		Integer index = 3;
		Double sumStere = 0.0;//方量
		Double sumPrice = 0.0;//总金额
		Integer sumTranNo = 0;//总车次
		Double sumWeight = 0.0;//总吨位

		Object[] billTitleObjs = {"工程名称","方量","标号","吨数","车数","单价", "运距", "结算运距", "金额","备注"};//表格副标题
		createDetailRow(sheet, 2, (short) 600, oneStyle, billTitleObjs);
		for (int i = 0; i < list.size(); i++) {
			ProductionWaterBill bill = list.get(i);
			sumStere = StringUtils.addDouble(sumStere, bill.getStere());
			sumPrice = StringUtils.addDouble(sumPrice, bill.getBillMoney());

			Object[] valueObjs = null;
			if (bill.getBillType().intValue() == 0) {//0：吨位结算，1：车次结算
				sumWeight = StringUtils.addDouble(sumWeight, bill.getWeight());
				valueObjs = new Object[]{bill.getConName(),bill.getStere(),bill.getLevel(),bill.getWeight(),"",
						bill.getPrice(), bill.getDistance(), bill.getBillDistance(), bill.getBillMoney(),bill.getRemark()};
			} else {
				sumTranNo += bill.getTranNo();
				valueObjs = new Object[]{bill.getConName(),bill.getStere(),bill.getLevel(),"",bill.getTranNo(),
						bill.getPrice(), bill.getDistance(), bill.getBillDistance(), bill.getBillMoney(),bill.getRemark()};
			}
			createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
			index++;
		}

		Object[] countObjs = {"小计",sumStere,"",sumWeight,sumTranNo,
				"", "","", sumPrice,""};
		createDetailRow(sheet, index, (short) 600, countStyle, countObjs);
		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);

		countObjs = new Object[]{"合计金额:","", sumPrice,"","",
				"大写:","",StringUtils.toMoneyUpperCase(sumPrice),"",""};
		createDetailRow(sheet, index, (short) 700, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"承租单位:","", list.get(0).getOrgFull(),"","",
				"出租单位:","",supplier,"",""};
		createDetailRow(sheet, index, (short) 700, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"制表:","", creator, "","", "经办人:","", "","",""};
		createDetailRow(sheet, index, (short) 700, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"生产部:","","", "", "", "负责人:","", "","",""};
		createDetailRow(sheet, index, (short) 700, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"财务部:","","", "", "", "财务部:", "","", "",""};
		createDetailRow(sheet, index, (short) 700, successStyle, countObjs);

		return wb;
	}


	/**
	 * 罐车运输结算表
	 * @param sheetName
	 * @param title
	 * @param today
	 * @param startTime
	 * @param endTime
	 * @param supplier
	 * @param creator
	 * @param mapBill
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook getTransportBillHSSFWorkbook(String sheetName, String title, String today, String startTime, String endTime,
															String supplier, String orgName, String creator, Map<String, List<ProductionTransportBill>> mapBill, XSSFWorkbook wb){
		if(wb == null){
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		sheet.setColumnWidth(0, 5590);
		sheet.setColumnWidth(1, 3670);
		sheet.setColumnWidth(2, 3670);
		sheet.setColumnWidth(3, 3670);
		sheet.setColumnWidth(4, 3670);
		sheet.setColumnWidth(5, 3670);
		sheet.setColumnWidth(6, 3670);
		sheet.setColumnWidth(7, 3670);

		//锁定列或行，参数1锁定左边列，参数2锁定行，参数3显示列，参数4显示行
		sheet.createFreezePane(0, 2, 0 ,2);
		//合并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 0, 7);//合并表头
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 0, 7);//结算时间
		sheet.addMergedRegion(region);
		//指定行高
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		titleStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueRightStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueRightStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		valueRightStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
		countStyle.setWrapText(true);//自动换行
//		countStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
//		countStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		XSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.RED.index, null));
		successStyle.setWrapText(true);//自动换行

		Object[] titleObjs = {title,"","","","","", "",""};
		createDetailRow(sheet, 0, (short) 600, titleStyle, titleObjs);
		Object[] billTimeObjs = {"结算时间:" + startTime.substring(0, 10) + "--" + endTime.substring(0, 10),"","","","","", "",""};
		valueRightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueRightStyle.setAlignment(HorizontalAlignment.RIGHT);
		createDetailRow(sheet, 1, (short) 500, valueRightStyle, billTimeObjs);

		Integer index = 2;//生成明细行下标
		Double sumStere = 0.0;//方量
		Double sumBillStere = 0.0;//结算方量
		Double sumPrice = 0.0;//总金额
		Double sumDistance = 0.0;
		Integer sumTranNo = 0;
		Double sumStere1 = 0.0;//方量
		Double sumBillStere1 = 0.0;//结算方量
		Double sumPrice1 = 0.0;//总金额
		Double sumDistance1 = 0.0;
		Integer sumTranNo1 = 0;

		Object[] billTitleObjs = {"工程名称","车次","实际方量","结算方量","公里数","单价", "结算金额","备注"};//表格副标题
		for (String key : mapBill.keySet()) {
			List<ProductionTransportBill> list = mapBill.get(key);
			for (int i = 0; i < list.size(); i++) {
				ProductionTransportBill bill = list.get(i);
				sumBillStere = StringUtils.addDouble(sumBillStere, bill.getBillStere());//结算方量
				sumStere = StringUtils.addDouble(sumStere, bill.getStere());//实际量
				sumPrice = StringUtils.addDouble(sumPrice, bill.getBillMoney());
				sumDistance = StringUtils.addDouble(sumDistance, bill.getDistance());
				sumTranNo += bill.getTranNo();

				String subTitle = "";
//				if (bill.getCarModel().intValue() == CarModelTypeEnum.QLHCY.getCode()) {
//					subTitle = "轻量化汇总";
//				} else {
//					subTitle = "中车汇总";
//				}
//				if (bill.getIsSpeed() == 0)
//					subTitle += "(主城方向及周边)";
//				else
//					subTitle += "(高速)";

				if (i == 0) {
					region=new CellRangeAddress(index, index, 0, 7);//列头
					sheet.addMergedRegion(region);
					Object[] billObjs = {subTitle,"","","","","", "",""};
					createDetailRow(sheet, index, (short) 500, titleStyle, billObjs);
					index++;
					createDetailRow(sheet, index, (short) 500, titleStyle, billTitleObjs);//副标题
					index++;
				}
				sumBillStere1 = StringUtils.addDouble(sumBillStere1, bill.getBillStere());
				sumStere1 = StringUtils.addDouble(sumStere1, bill.getStere());
				sumPrice1 = StringUtils.addDouble(sumPrice1, bill.getBillMoney());
				sumDistance1 = StringUtils.addDouble(sumDistance1, bill.getDistance());
				sumTranNo1 += bill.getTranNo();
				Object[] billObjs = {bill.getConName(),bill.getTranNo(),bill.getStere(),bill.getBillStere(),bill.getDistance(),
						bill.getPrice(), bill.getBillMoney(),bill.getRemark()};
				createDetailRow(sheet, index, (short) 500, valueStyle, billObjs);
				index++;

			}

			Object[] sumObjs = {"小计", sumTranNo1, sumStere1, sumBillStere1, sumDistance1,"", sumPrice1,""};
			createDetailRow(sheet, index, (short) 500, successStyle, sumObjs);
			index++;

			sumStere1 = 0.0; sumDistance1 = 0.0; sumPrice1=0.0; sumTranNo1=0;sumBillStere1=0.0;
		}

		region=new CellRangeAddress(index, index, 1, 3);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 7);//合并合计
		sheet.addMergedRegion(region);

		Object[] countObjs = new Object[]{"合计金额:", sumPrice,"","",
				"大写:",StringUtils.toMoneyUpperCase(sumPrice),"",""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 3);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 7);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"承租单位:", orgName,"","",
				"出租单位:",supplier,"",""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 3);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 7);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"制表:", creator, "","", "经办人:","", "",""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 3);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 7);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"生产部:","", "", "", "负责人:","", "",""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 3);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 7);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"财务部:","", "", "", "财务部:", "","", ""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		return wb;
	}

	/**
	 * 导出泵送结算单报表
	 * @param sheetName
	 * @param title
	 * @param list
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook getPumpBillHSSFWorkbook(String sheetName, String title, Double subsidy, String startTime, String endTime,
													   String supplier, String creator,List<ProductionPumpBill> list, XSSFWorkbook wb){
		if(wb == null){
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		sheet.setColumnWidth(0, 5590);
		sheet.setColumnWidth(1, 7590);
		sheet.setColumnWidth(2, 5590);
		sheet.setColumnWidth(3, 3670);
		sheet.setColumnWidth(4, 5590);
		sheet.setColumnWidth(5, 5590);

		//合并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 0, 5);//合并表头
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 0, 3);//结算时间
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 4, 5);//结算时间
		sheet.addMergedRegion(region);
		//指定行高
		XSSFCellStyle oneStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		oneStyle.setFont(ExcelStyleUtils.hssfFont(wb, 18, "宋体", IndexedColors.BLACK.index, true));
		oneStyle.setWrapText(true);//自动换行
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 18, "宋体", IndexedColors.BLACK.index, true));
		titleStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueRightStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueRightStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueRightStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueLeftStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueLeftStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		valueLeftStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
		countStyle.setWrapText(true);//自动换行
//		countStyle.setVerticalAlignment(XSSFCellStyle.ALIGN_LEFT);
//		countStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.RED.index, true));
		XSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		successStyle.setWrapText(true);//自动换行

		Object[] titleObjs = {title,"","","","",""};
		createDetailRow(sheet, 0, (short) 600, titleStyle, titleObjs);
		valueRightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueRightStyle.setAlignment(HorizontalAlignment.RIGHT);

		valueLeftStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueLeftStyle.setAlignment(HorizontalAlignment.LEFT);
		Object[] billTimeObjs = {"承租方:" + list.get(0).getOrgFull(),"","","","制表时间:" + DateUtil.convertString(new Date(), "yyyy.MM.dd"),""};
		XSSFCellStyle[] cellStyles = {valueLeftStyle,valueLeftStyle,valueLeftStyle,valueLeftStyle,valueRightStyle,valueRightStyle};
		createDetailRowV1(sheet, 1, (short) 500, cellStyles, billTimeObjs);
		Object[] subTitleObjs = {"泵送方式","时间","泵送方量(m³)","单价(元)","金额(元)","备注"};
		createDetailRow(sheet, 2, (short) 600, successStyle, subTitleObjs);

		Integer index = 3;
		Double sumStere = 0.0;//方量
		Double sumPrice = 0.0;//总金额
		String time = startTime.substring(0, 10).replaceAll("-", ".") + "--"
				+ endTime.substring(0, 10).replaceAll("-", ".") ;
		for (int i = 0; i < list.size(); i++) {
			ProductionPumpBill bill = list.get(i);
			sumStere = StringUtils.addDouble(sumStere, bill.getStere());
			sumPrice = StringUtils.addDouble(sumPrice, bill.getBillMoney());

			Object[] valueObjs = {bill.getPumpType(), time,bill.getStere(),
					bill.getPrice(), bill.getBillMoney(), bill.getRemark()};
			createDetailRow(sheet, index, (short) 500, successStyle, valueObjs);
			index++;
		}

		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		Object[] countObjs = {"小计:", "", sumStere, "", sumPrice, ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;
		if (!StringUtils.isNull(subsidy) && subsidy > 0) {
			sumPrice = StringUtils.addDouble(sumPrice, subsidy);
			region=new CellRangeAddress(index, index, 0, 1);//合并合计
			sheet.addMergedRegion(region);
			countObjs = new Object[] {"补贴(元):", "", "", "", subsidy, ""};
			createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
			index++;
		}
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[] {"应付款合计(元):", "", "", "", sumPrice, ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;

		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[] {"结算实付金额:", "", StringUtils.toMoneyUpperCase(StringUtils.formatDouble(sumPrice, 2)),
				"", "", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);
		index++;

		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"承租单位:", list.get(0).getOrgFull(),"",
				"出租单位:",supplier,""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"制表:", creator, "", "经办人:", "", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"生产部:","",  "", "审核:","", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 1, 2);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 4, 5);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"财务部:","", "", "结算日期:","", ""};
		createDetailRow(sheet, index, (short) 600, successStyle, countObjs);

		return wb;
	}

	/**
	 * 导出砂浆结算单报表
	 * @param sheetName
	 * @param title
	 * @param list
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook getMortarBillHSSFWorkbook(String sheetName, String title, String today, String startTime, String endTime,
								 String supplier, String creator, List<ProductionMortarBill> list,Map<Integer, String> billTypeMap,
								 XSSFWorkbook wb){
		if(wb == null){
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		sheet.setColumnWidth(0, 3590);
		sheet.setColumnWidth(1, 5590);
		sheet.setColumnWidth(2, 5590);
		sheet.setColumnWidth(3, 3670);
		sheet.setColumnWidth(4, 3670);
		sheet.setColumnWidth(5, 3670);
		sheet.setColumnWidth(6, 3670);
		sheet.setColumnWidth(7, 3670);
		sheet.setColumnWidth(8, 3670);
		sheet.setColumnWidth(9, 3670);

		//合并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 0, 9);//合并表头
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(1, 1, 0, 9);//结算时间
		sheet.addMergedRegion(region);
		//指定行高
		XSSFCellStyle oneStyle = ExcelStyleUtils.cellStyle(wb, true, true,
			true, true, true, true, true, null);
		oneStyle.setFont(ExcelStyleUtils.hssfFont(wb, 18, "宋体", IndexedColors.BLACK.index, true));
		oneStyle.setWrapText(true);//自动换行
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
			true, true, true, true, true, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		titleStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
			true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle valueRightStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueRightStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		valueRightStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
			true, true, true, true, true, null);
		countStyle.setWrapText(true);//自动换行
//		countStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
//		countStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		XSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
			true, true, true, true, true, IndexedColors.LIME.index);
		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, null));
		successStyle.setWrapText(true);//自动换行

		Object[] titleObjs = {title,"","","","","", "","","",""};
		createDetailRow(sheet, 0, (short) 600, titleStyle, titleObjs);
		Object[] billTimeObjs = {"结算时间:" + startTime.substring(0, 10) + "--" + endTime.substring(0, 10),"","","","","", "","","",""};
		valueRightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		valueRightStyle.setAlignment(HorizontalAlignment.RIGHT);
		createDetailRow(sheet, 1, (short) 500, valueRightStyle, billTimeObjs);

		Integer index = 2;//生成明细行下标
		Double sumStere = 0.0;//方量
		Double sumBillStere = 0.0;//结算方量
		Double sumPrice = 0.0;//总金额
		Double sumDistance = 0.0;
		Double sumStere1 = 0.0;//方量
		Double sumBillStere1 = 0.0;//结算方量
		Double sumPrice1 = 0.0;//总金额
		Double sumDistance1 = 0.0;
		Integer billType = -1;//结算类型
		Integer isSpeed = -1;//是否是高速
		for (int i = 0; i < list.size(); i++) {
			ProductionMortarBill bill = list.get(i);
			sumBillStere = StringUtils.addDouble(sumBillStere, bill.getBillStere());//结算方量
			sumStere = StringUtils.addDouble(sumStere, bill.getStere());//实际量
			sumPrice = StringUtils.addDouble(sumPrice, bill.getBillMoney());
			sumDistance = StringUtils.addDouble(sumDistance, bill.getDistance());

			String subTitle = billTypeMap.get(bill.getBillType()) + "汇总";
			if (bill.getIsSpeed() == 0)
				subTitle += "(主城方向及周边)";
			else
				subTitle += "(高速)";
			if (i == 0) {
				billType = bill.getBillType();
				isSpeed = bill.getIsSpeed();
				region=new CellRangeAddress(index, index, 0, 9);//列头
				sheet.addMergedRegion(region);
				Object[] billObjs = {subTitle,"","","","","", "","","",""};
				createDetailRow(sheet, index, (short) 500, titleStyle, billObjs);
				index++;
				Object[] billTitleObjs = convertMortarTitle(bill.getBillType(), bill.getIsSpeed());//表格副标题
				if (bill.getBillType().intValue() == MortarBillTypeEnum.SJ.getCode()
						|| bill.getBillType().intValue() == MortarBillTypeEnum.SP.getCode()) {
					if (bill.getIsSpeed() == 0) {
						region=new CellRangeAddress(index, index, 8, 9);//合并表头
						sheet.addMergedRegion(region);
					} else {
						region=new CellRangeAddress(index, index, 7, 9);//合并表头
						sheet.addMergedRegion(region);
					}
				} else if (bill.getBillType().intValue() == MortarBillTypeEnum.FL.getCode()) {
					region=new CellRangeAddress(index, index, 8, 9);//合并表头
					sheet.addMergedRegion(region);
				}
				createDetailRow(sheet, index, (short) 500, titleStyle, billTitleObjs);
				index++;
			} else {
				if (billType.intValue() != bill.getBillType().intValue()) {
					CreateMortarCount(billType, isSpeed, sumStere1, sumBillStere1,
							sumDistance1, sumPrice1, index, valueStyle, sheet);
					index++;
					sumStere1 = 0.0; sumDistance1 = 0.0; sumPrice1=0.0; sumBillStere1=0.0;
					region=new CellRangeAddress(index, index, 0, 9);//列头
					sheet.addMergedRegion(region);
					Object[] billObjs = {subTitle,"","","","","", "","","",""};
					createDetailRow(sheet, index, (short) 500, titleStyle, billObjs);
					index++;
					Object[] billTitleObjs = convertMortarTitle(bill.getBillType(), bill.getIsSpeed());
					if (bill.getBillType().intValue() == MortarBillTypeEnum.SJ.getCode()
							|| bill.getBillType().intValue() == MortarBillTypeEnum.SP.getCode()) {
						if (bill.getIsSpeed() == 0) {
							region=new CellRangeAddress(index, index, 8, 9);//合并表头
							sheet.addMergedRegion(region);
						} else {
							region=new CellRangeAddress(index, index, 7, 9);//合并表头
							sheet.addMergedRegion(region);
						}
					} else if (bill.getBillType().intValue() == MortarBillTypeEnum.FL.getCode()) {
						region=new CellRangeAddress(index, index, 8, 9);//合并表头
						sheet.addMergedRegion(region);
					}
					createDetailRow(sheet, index, (short) 500, titleStyle, billTitleObjs);
					index++;
					billType = bill.getBillType();
					isSpeed = bill.getIsSpeed();
				} else if (bill.getIsSpeed().intValue() != isSpeed.intValue()) {//运输类型不同
					CreateMortarCount(billType, isSpeed, sumStere1, sumBillStere1,
							sumDistance1, sumPrice1, index, valueStyle, sheet);
					index++;
					sumStere1 = 0.0; sumDistance1 = 0.0; sumPrice1=0.0; sumBillStere1=0.0;
					region=new CellRangeAddress(index, index, 0, 9);//列头
					sheet.addMergedRegion(region);
					Object[] billObjs = {subTitle,"","","","","", "","","",""};
					createDetailRow(sheet, index, (short) 500, titleStyle, billObjs);
					index++;
					//结算表表头
					if (bill.getBillType().intValue() == MortarBillTypeEnum.SJ.getCode()
							|| bill.getBillType().intValue() == MortarBillTypeEnum.SP.getCode()) {
						if (bill.getIsSpeed() == 0) {
							region=new CellRangeAddress(index, index, 8, 9);//合并表头
							sheet.addMergedRegion(region);
						} else {
							region=new CellRangeAddress(index, index, 7, 9);//合并表头
							sheet.addMergedRegion(region);
						}
					} else if (bill.getBillType().intValue() == MortarBillTypeEnum.FL.getCode()) {
						region=new CellRangeAddress(index, index, 8, 9);//合并表头
						sheet.addMergedRegion(region);
					}
					Object[] billTitleObjs = convertMortarTitle(bill.getBillType(), bill.getIsSpeed());
					createDetailRow(sheet, index, (short) 500, titleStyle, billTitleObjs);
					index++;
					isSpeed = bill.getIsSpeed();
				}
			}
			sumBillStere1 = StringUtils.addDouble(sumBillStere1, bill.getBillStere());
			sumStere1 = StringUtils.addDouble(sumStere1, bill.getStere());
			sumPrice1 = StringUtils.addDouble(sumPrice1, bill.getBillMoney());
			sumDistance1 = StringUtils.addDouble(sumDistance1, bill.getDistance());

			CreateMortarValue(bill, index, valueStyle, sheet);
			index++;
			if (i == list.size() -1) {
				CreateMortarCount(billType, isSpeed, sumStere1, sumBillStere1,
						sumDistance1, sumPrice1, index, valueStyle, sheet);
				index++;
			}
		}

		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);

		Object[] countObjs = new Object[]{"合计金额:","", sumPrice,"", "",
				"大写:","",StringUtils.toMoneyUpperCase(sumPrice),"", ""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"承租单位:","", list.get(0).getOrgFull(),"", "",
				"出租单位:","",supplier,"", ""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"制表:", creator, "","", "", "经办人:","", "","", ""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"生产部:","", "","", "", "负责人:","", "","", ""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		index++;
		region=new CellRangeAddress(index, index, 0, 1);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 2, 4);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 5, 6);//合并合计
		sheet.addMergedRegion(region);
		region=new CellRangeAddress(index, index, 7, 9);//合并合计
		sheet.addMergedRegion(region);
		countObjs = new Object[]{"财务部:","", "","", "", "财务部:","", "","", ""};
		createDetailRow(sheet, index, (short) 700, countStyle, countObjs);

		return wb;
	}
	private static void createDetailRow(XSSFSheet sheet,int rowIndex,short rowHeight,XSSFCellStyle cellStyle,Object...cellValues) {
		XSSFRow row = sheet.createRow(rowIndex);
		row.setHeight((short) rowHeight);
		for (int i = 0; i < cellValues.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyle);
			if (StringUtils.isNull(cellValues[i]))
				cell.setCellValue("");
			else {
				if (cellValues[i] instanceof String)
					cell.setCellValue(StringUtils.convertString(cellValues[i]));
				else if (cellValues[i] instanceof Double)
					cell.setCellValue(StringUtils.convertDouble(cellValues[i]));
				else if (cellValues[i] instanceof Integer)
					cell.setCellValue(StringUtils.convertInteger(cellValues[i]));
			}
		}
	}
	private static void createDetailRowV1(XSSFSheet sheet,int rowIndex,short rowHeight,XSSFCellStyle[] cellStyle,Object...cellValues) {
		XSSFRow row = sheet.createRow(rowIndex);
		row.setHeight((short) rowHeight);
		for (int i = 0; i < cellValues.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyle[i]);
			if (StringUtils.isNull(cellValues[i]))
				cell.setCellValue("");
			else {
				if (cellValues[i] instanceof String)
					cell.setCellValue(StringUtils.convertString(cellValues[i]));
				else if (cellValues[i] instanceof Double)
					cell.setCellValue(StringUtils.convertDouble(cellValues[i]));
				else if (cellValues[i] instanceof Integer)
					cell.setCellValue(StringUtils.convertInteger(cellValues[i]));
			}
		}
	}

	/**
	 * 创建明细行
	 * @param sheet
	 * @param rowIndex
	 * @param rowHeight
	 * @param cellValues
	 */
	private static XSSFRow createOutDetailRow(XSSFWorkbook wb,XSSFSheet sheet,int rowIndex,short rowHeight,XSSFCellStyle cellStyle,Object...cellValues){
		if (cellValues == null || cellValues.length < 1){
			return null;
		}
		XSSFRow row = createRow(sheet, rowIndex, rowHeight);
		int curCellIndex = 0;
		for (Object cellValue : cellValues){
			XSSFCell cell = row.createCell(curCellIndex);
			if (cellValue instanceof BigDecimal){
				BigDecimal tcv = (BigDecimal)cellValue;
				BigDecimal bigDecimal = tcv.setScale(2, BigDecimal.ROUND_HALF_UP);
				cell.setCellValue(bigDecimal.toString());
			}else if (cellValue instanceof Double){
				cell.setCellValue((Double)cellValue);
			}else {
				cell.setCellValue(String.valueOf(cellValue));
			}
			if (cellStyle != null){
				cell.setCellStyle(cellStyle);
			}
			curCellIndex++;
		}
		return row;
	}

	/**
	 * 创建行
	 * @param sheet				表
	 * @param currentRowIndex	行索引
	 * @param rowHeight			行高
	 * @return
	 */
	private static XSSFRow createRow(XSSFSheet sheet, int currentRowIndex, short rowHeight) {
		XSSFRow orgRow = sheet.createRow(currentRowIndex);
		orgRow.setHeight(rowHeight);
		return orgRow;
	}

	/**
	 * 转换砂浆、水票标题抬头
	 * @param billType
	 * @param isSpeed
	 * @return
	 */
	private static Object[] convertMortarTitle(Integer billType, Integer isSpeed) {
		if (billType.intValue() == MortarBillTypeEnum.SJ.getCode()
				|| billType.intValue() == MortarBillTypeEnum.SP.getCode()) {
			if (isSpeed == 0) {
				Object[] billTitleObjs = {"日期","工程名称","车号","实际方量", "结算方量","运距","单价", "总金额","备注", ""};
				return billTitleObjs;
			} else {
				Object[] billTitleObjs = {"日期","工程名称","车号","实际方量", "运距","单价", "总金额","备注", "", ""};
				return billTitleObjs;
			}
		} else if (billType.intValue() == MortarBillTypeEnum.ZL.getCode()) {
			Object[] billTitleObjs = {"日期","转入工程","转出工程", "车号","实际方量", "结算方量", "运距","单价", "总金额","备注"};
			return billTitleObjs;
		} else if (billType.intValue() == MortarBillTypeEnum.FL.getCode()) {
			Object[] billTitleObjs = {"日期","工程名称","车号","实际方量", "结算方量","运距","单价", "总金额","备注", ""};
			return billTitleObjs;
		} else {
			Object[] billTitleObjs = {"日期","工程名称","车号","实际方量", "结算方量","运距","单价", "总金额","备注", ""};
			return billTitleObjs;
		}
	}

	/**
	 * 创建砂浆合计行
	 * @param billType 结算类型
	 * @param isSpeed 是否高速
	 * @param stere 方量
	 * @param billStere 结算方量
	 * @param distance 运距
	 * @param price 合计金额
	 * @param index 行下标
	 * @param valueStyle
	 * @param sheet
	 */
	private static void CreateMortarCount(Integer billType, Integer isSpeed, Double stere, Double billStere,
										  Double distance, Double price,  Integer index, XSSFCellStyle valueStyle,
										  XSSFSheet sheet) {
		if (billType.intValue() == MortarBillTypeEnum.SJ.getCode()
				|| billType.intValue() == MortarBillTypeEnum.SP.getCode()) {
			if (isSpeed.intValue() == 0) {//非高速
				CellRangeAddress region=new CellRangeAddress(index, index, 8, 9);//合并表头
				sheet.addMergedRegion(region);
				Object[] countObjs = {"小计","","", stere, billStere, distance,"", price, "", ""};
				createDetailRow(sheet, index, (short) 500, valueStyle, countObjs);
			} else {
				CellRangeAddress region=new CellRangeAddress(index, index, 7, 9);//合并表头
				sheet.addMergedRegion(region);
				Object[] countObjs = {"小计", "", "", billStere, distance,"", price, "", "", ""};
				createDetailRow(sheet, index, (short) 500, valueStyle, countObjs);
			}
		} else if (billType.intValue() == MortarBillTypeEnum.ZL.getCode()) {
			Object[] countObjs = {"小计","","", "", stere, billStere, distance,"", price,""};
			createDetailRow(sheet, index, (short) 500, valueStyle, countObjs);
		} else if (billType.intValue() == MortarBillTypeEnum.FL.getCode()) {
			CellRangeAddress region=new CellRangeAddress(index, index, 8, 9);//合并表头
			sheet.addMergedRegion(region);
			Object[] countObjs = {"小计","","", stere, billStere, distance,"", price,"", ""};
			createDetailRow(sheet, index, (short) 500, valueStyle, countObjs);
		} else {
			CellRangeAddress region=new CellRangeAddress(index, index, 8, 9);//合并表头
			sheet.addMergedRegion(region);
			Object[] countObjs = {"小计","","",stere, billStere, distance,"", price,"", ""};
			createDetailRow(sheet, index, (short) 500, valueStyle, countObjs);
		}
	}
	private static void CreateMortarValue(ProductionMortarBill bill, Integer index, XSSFCellStyle valueStyle,
										  XSSFSheet sheet) {
		String time = bill.getStartTime().substring(5,10).replace("-", "/");
		String conName = bill.getConName();
		String targetConName = "";//转入工程
		String code = bill.getCode();//车号
		if (!StringUtils.isNull(bill.getCarNo()))
			code += "(" + bill.getCarNo() + ")";
		Double stere = bill.getStere();
		Double billStere = bill.getBillStere();
		Double distance = bill.getDistance();
		Double price = bill.getPrice();
		Double billMoney = bill.getBillMoney();
		String remark = bill.getRemark();
		if (bill.getBillType().intValue() == MortarBillTypeEnum.SJ.getCode()
				|| bill.getBillType().intValue() == MortarBillTypeEnum.SP.getCode()) {
			if (bill.getIsSpeed() == 0) {
				CellRangeAddress region=new CellRangeAddress(index, index, 8, 9);//合并表头
				sheet.addMergedRegion(region);
				Object[] valueObjs = {time,conName,code, stere, billStere,distance,price, billMoney,remark, ""};
				createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
			} else {
				CellRangeAddress region=new CellRangeAddress(index, index, 7, 9);//合并表头
				sheet.addMergedRegion(region);
				Object[] valueObjs = {time,conName,code,billStere, distance,price, billMoney,remark, "", ""};
				createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
			}
		} else if (bill.getBillType().intValue() == MortarBillTypeEnum.ZL.getCode()) {
			String[] str = conName.split(",");
			if (str != null && str.length == 2) {
				conName = str[0];
				targetConName = str[1];
			}
			Object[] valueObjs = {time,conName, targetConName, code,stere, billStere,distance,price, billMoney,remark};
			createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
		} else if (bill.getBillType().intValue() == MortarBillTypeEnum.FL.getCode()) {
			CellRangeAddress region=new CellRangeAddress(index, index, 8, 9);//合并表头
			sheet.addMergedRegion(region);
			Object[] valueObjs = {time,conName,code,stere, billStere,distance,price, billMoney,remark, ""};
			createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
		} else {
			CellRangeAddress region=new CellRangeAddress(index, index, 8, 9);//合并表头
			sheet.addMergedRegion(region);
			Object[] valueObjs = {time,conName,code,stere, billStere,distance,price, billMoney,remark, ""};
			createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
		}
	}

}
